package model;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Collections;
import java.util.LinkedList;
import java.util.List;

public class TelephoneTypesDAO {

	private List<TelephoneTypes> elementsList;
	private Connection con;
	private int maxId;
	private DatabaseCommons dbc = new DatabaseCommons();

	public TelephoneTypesDAO() {
		/*
		 * 2013.03.11 - changing ArrayList to LinkedList since the latter is
		 * optimized for addition or subtraction of items
		 */
		elementsList = new LinkedList<TelephoneTypes>();
	}

	public TelephoneTypesDAO(LinkedList<TelephoneTypes> elements) {
		super();
		this.elementsList = elements;
	}

	public void addElement(TelephoneTypes element) {
		elementsList.add(element);
		/*
		 * System.out.println(
		 * "addTelephoneTypes succeeded!\n*************TelephoneTypes added is:\n"
		 * + "idTelephoneTypes: " + p.getId() + ", name: " + p.getName() +
		 * ", surname: " + p.getSurname() + ", Collateral client: " + p.getCc()
		 * + ", CC desc: " + p.getColClient() + ", isChkIC? = " +
		 * p.isChkIndirectClient());
		 */
	}

	public void removeElement(int index) {
		elementsList.remove(index);
	}

	public List<TelephoneTypes> getElements() {
		// Collections.unmodifiableList prevents other classes from modifying
		// the list by the reference retrieved
		return Collections.unmodifiableList(elementsList);
	}

	public void h2Connect() throws SQLException {
		// checks the mysql-connector
		try {
			Class.forName("org.h2.Driver");
		} catch (ClassNotFoundException e) {
			System.out
					.println("ClassNotFoundException while seeking the H2 driver: "
							+ e);
		}
		// create connection
		String url = "jdbc:h2:~/test", sqlUname = "sa", sqlPass = "";
		// changing url from embedded to server mode
		url = "jdbc:h2:tcp://localhost/~/test";
		// sqlPass = "r00t";

		// if connection is already created, just return
		if (con != null)
			return;
		// otherwise, create it
		con = DriverManager.getConnection(url, sqlUname, sqlPass);
		System.out.println("Connected: " + con);
	}

	public void disconnect() throws SQLException {
		System.out.println("...db connection closing now.");
		if (con != null) {
			con.close();
			System.out.println("connection is closed now? " + con.isClosed());
		}
	}

	/*
	 * Method to insert and or update a list of people
	 */
	public void saveToDB() throws SQLException {
		String csql, isql, usql, pname, psurname;
		int col = 0, colClient = 0;
		boolean isChkIndirectClient;
		/*
		 * id will be used to check the id's while I go through the elements
		 * reps to compare occurrences
		 */
		int id = -1, reps = -1;
		/*
		 * sql queries with question marks are used in prepared statements since
		 * they are more secure due to being more difficult to be attacked by a
		 * code injection tactic.
		 */
		csql = "select count(*) as reps from People where id_TelephoneTypes = ?";
		isql = "insert into People (id_TelephoneTypes, name, surname, collateral_client, is_direct_client) values(?, ?, ?, ?, ?)";
		usql = "update People set name = ?, surname = ?, collateral_client = ?, is_direct_client = ? where id_TelephoneTypes = ?";
		PreparedStatement cps = null, eps = null;
		ResultSet rs = null;

		cps = con.prepareStatement(csql);

		for (TelephoneTypes p : elementsList) {
			id = p.getId();
			pname = p.getDesc();

			System.out.println("values created for insertion - id: " + id
					+ ", pname: " + pname);
			// first parameter is the index of the wildcard, this once there's
			// just one though
			cps.setInt(1, id);
			// execute the query
			rs = cps.executeQuery();
			// go to cursor in the first position
			rs.next();
			// get the count
			reps = rs.getInt("reps");

			if (reps == 0) {
				// preparing the insert with the execution prepared statement
				eps = con.prepareStatement(isql);
				// col will iterate setting values through the column numbers
				// dynamically
				col = 1;
				eps.setInt(col++, id);
				eps.setString(col++, pname);

				System.out.println("Inserting TelephoneTypes with id: " + id
						+ " (" + pname);
				eps.executeUpdate();
			} else {
				// preparing the update with the execution prepared statement
				eps = con.prepareStatement(usql);
				// col will iterate setting values through the column numbers
				// dynamically
				col = 1;
				eps.setString(col++, pname);
				eps.setInt(col++, id);

				System.out.println("Updating TelephoneTypes with id: " + id
						+ " (" + pname);
				eps.executeUpdate();
			}
		}
		csql = "cleaned from saveToDB()";
		isql = "cleaned from saveToDB()";
		usql = "cleaned from saveToDB()";
		cps.close();
		eps.close();
	}

	public void loadFromDB() throws SQLException {
		String csql, isql, usql, pname, psurname;
		int col = 0;

		csql = "select id_telephone_type, telephone_type from telephone_types order by telephone_type, id_telephone_type";

		/*
		 * trying to factor connection methods
		 */
		dbc.h2Connect();
		con = dbc.getConnection();

		Statement select = con.createStatement();

		elementsList.clear();

		// I execute the query and load it in the rs resultset
		ResultSet rs = select.executeQuery(csql);

		while (rs.next()) {
//			System.out.println(TelephoneTypesDAO.class.toString()
//					+ " elements - id_telephone_type: " + rs.getInt(1)
//					+ ", telephone_type: '" + rs.getString(2) + "'");

			TelephoneTypes retrievedElement = new TelephoneTypes(rs.getInt(1),
					rs.getString(2));
			elementsList.add(retrievedElement);
		}

		/*
		 * finally, I close the resultset and the statement
		 */
		csql = "cleaned from loadFromDB()";
		select.close();
		rs.close();

	}

	public int getMaxId() throws SQLException {
		String csql, isql, usql, pname, psurname;
		int col = 0;
		/*
		 * getting max id
		 */
		csql = "select max(id_TelephoneTypes) as max_id from people";
		Statement select = con.createStatement();
		// first thing, I'll clear the list
		elementsList.clear();

		// I execute the query and load it in the rs resultset
		ResultSet rs = select.executeQuery(csql);

		while (rs.next()) {
			maxId = rs.getInt("max_id");
			// System.out.println("TelephoneTypesDAO.getMaxId: rs(maxId): " +
			// maxId);
		}

		/*
		 * finally, I close the resultset and the statement
		 */
		csql = "cleaned from loadFromDB()";
		select.close();
		rs.close();
		return maxId;
	}

}
